# 导入依赖包
import pandas as pd
import pymysql

data = pd.read_parquet("../static/data/user_behavior_20141118_20141218_edit.parquet")
print(data.shape)  # 输出 (12256906, 5)
print(data.head())

# 整体数据规模
# 整体数据规模
total_records = len(data)
total_users = len(set(data['user_id']))
total_items = len(set(data['item_id']))
total_categories = len(set(data['item_category']))
print("整体数据的规模为：", total_records, "条数据，", total_users, "个用户，", total_items, "个商品，", total_categories,
      "个类别")
'''
# 连接到 MySQL
host = 'localhost'
user = 'root'
password = '123456'
database = 'user_behavior_db_2014'
connection = pymysql.connect(host=host, user=user, password=password, database=database)
cursor = connection.cursor()

insert_query = """
INSERT INTO data_summary (total_records, total_users, total_items, total_categories)
VALUES (%s, %s, %s, %s);
"""
cursor.execute(insert_query, (total_records, total_users, total_items, total_categories))
connection.commit()
'''

from pyecharts import options as opts
from pyecharts.charts import Pie

# 映射关系：将 behavior_type 从英文映射为中文
behavior_mapping = {
    "pv": "浏览",
    "buy": "购买",
    "cart": "加入购物车",
    "fav": "收藏"
}
data['behavior_type'] = data['behavior_type'].map(behavior_mapping)
# 按照行为类型统计数量
behavior_counts = data['behavior_type'].value_counts()
# 准备数据
behavior_labels = behavior_counts.index.tolist()
behavior_values = behavior_counts.values.tolist()

# 创建饼图，用户行为分布
pie_chart = (
    Pie()
    .add("",
         [list(z) for z in zip(behavior_labels, behavior_values)],
         radius=["30%", "75%"],
         center=["50%", "50%"],
         )
    .set_global_opts(
        # 标题居中
        title_opts=opts.TitleOpts(title="用户行为分布"),
        legend_opts=opts.LegendOpts(orient="vertical", pos_top="10%", pos_left="5%"),
    )
    .set_series_opts(
        label_opts=opts.LabelOpts(formatter="{b}: {c} ({d}%)")
    )
)
# 渲染图表为 HTML 文件
pie_chart.render("../static/html/user_behavior_pie_chart.html")
'''
# 将饼图的四个数据新建表存储到MySQL
insert_query = """
INSERT INTO user_behavior_distribution (behavior_type, count)
VALUES (%s, %s);
"""
for behavior, count in zip(behavior_labels, behavior_values):
    cursor.execute(insert_query, (behavior, count))
connection.commit()

# 关闭连接
cursor.close()
connection.close()
'''